package com.linkoog.devtools.code_generator.database.dao.db;

import com.linkoog.devtools.code_generator.database.domain.ColumnInfo;
import com.linkoog.devtools.code_generator.database.domain.ConnectionInfo;
import com.linkoog.devtools.code_generator.database.domain.DatabaseInfo;
import com.linkoog.devtools.code_generator.database.domain.DbType;
import com.linkoog.devtools.code_generator.database.domain.TableInfo;
import com.linkoog.devtools.code_generator.database.dao.AbstractDatabaseDao;
import com.linkoog.devtools.code_generator.setting.persistent.CodeGenDevToolsPersistent;
import com.linkoog.devtools.code_generator.util.BooleanUtils;
import com.linkoog.devtools.code_generator.util.StringUtil;
import com.intellij.openapi.application.ApplicationManager;
import org.apache.commons.lang3.StringUtils;
import org.jetbrains.annotations.NotNull;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class PostgreDatabaseDao extends AbstractDatabaseDao {


    public PostgreDatabaseDao(@NotNull ConnectionInfo connectionInfo) {
        super(connectionInfo);
    }

    @Override
    public boolean validConnection() {
        Connection conn = getConnection();
        try (Statement statement = conn.createStatement()){
            return statement.execute("select 1");
        } catch (SQLException e) {
            return false;
        } finally {
            closeConnection(conn);
        }
    }

    @Override
    public List<DatabaseInfo> getDatabaseInfoList() {
        Connection conn = getConnection();
        List<DatabaseInfo> rs = new ArrayList<>();
        try (Statement statement = conn.createStatement()){
            statement.execute("SELECT datname FROM pg_database");
            ResultSet resultSet = statement.getResultSet();
            while (resultSet.next()) {
                String db = resultSet.getString("datname");
                if (StringUtils.equalsIgnoreCase(db, "template0") || StringUtils.equalsIgnoreCase(db, "template1")
                        || StringUtils.equalsIgnoreCase(db, "postgres")) {
                    continue;
                }

                DatabaseInfo databaseInfo = new DatabaseInfo();
                databaseInfo.databaseName = db;
                databaseInfo.schemaList = getSchemaList(db);
                rs.add(databaseInfo);
            }
            return rs;
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    @Override
    public List<String> getSchemaList(String database) {
        Connection conn = getConnection(database);
        List<String> rs = new ArrayList<>();
        try (Statement statement = conn.createStatement()){
            statement.execute("SELECT nspname from pg_namespace  WHERE nspname NOT IN ('pg_toast','pg_catalog','information_schema') and nspname not like'pg_temp_%' and nspname not like 'pg_toast_temp_%'");
            ResultSet resultSet = statement.getResultSet();
            while (resultSet.next()) {
                String schema = resultSet.getString("nspname");
                rs.add(schema);
            }
            return rs;
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    @Override
    public List<TableInfo> getTableInfoList(String database, String schema) {
        Connection conn = getConnection(database);
        List<TableInfo> rs = new ArrayList<>();
        String sql = "SELECT a.schemaname, a.tablename AS table_name, obj_description(relfilenode,'pg_class') AS table_comment FROM pg_tables a LEFT JOIN  pg_class b ON a.tablename = b.relname WHERE a.schemaname = ? ORDER BY a.tablename";
        try (PreparedStatement preparedStatement =  conn.prepareStatement(sql)){
            preparedStatement.setString(1,  schema);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(resultSet.getString("table_name"));
                tableInfo.setTableComment(resultSet.getString("table_comment"));

                ApplicationManager.getApplication().executeOnPooledThread(() -> {
                    tableInfo.setJavaEntityName(StringUtil.textToCamelCase(tableInfo.getTableName(),true));
                    tableInfo.setColumnInfos(getColumnInfoList(database,schema, tableInfo.getTableName()));
                });

                rs.add(tableInfo);
            }
            return rs;
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    @Override
    public List<ColumnInfo> getColumnInfoList(String database, String schema, String table) {
        Connection conn = getConnection(database);
        List<ColumnInfo> rs = new ArrayList<>();

        String sql = "SELECT m.schemaname, a.attname as column_name, "
                + " concat_ws('', t.typname, SUBSTRING(pg_catalog.format_type(a.atttypid, a.atttypmod) from '\\(.*\\)')) as column_type,"
                + " t.typname AS data_type,"
                + " (a.attnotnull IS false) as is_nullable,"
                + " col_description(a.attrelid,a.attnum) as column_comment "
                + " FROM pg_tables AS m  "
                + " LEFT JOIN  pg_class AS C ON m.tablename = c.relname "
                + " LEFT JOIN pg_attribute AS a ON a.attrelid = c.oid "
                + " LEFT JOIN pg_type t ON a.atttypid = t.oid "
                + " where m.schemaname = ? and m.tablename = ?  and a.attnum > 0 ";
        try (PreparedStatement preparedStatement =  conn.prepareStatement(sql)){
            preparedStatement.setString(1, schema);
            preparedStatement.setString(2, table);
            ResultSet resultSet =  preparedStatement.executeQuery();

            Map<String,String> mapping = CodeGenDevToolsPersistent.getInstance().getTypeMappings(DbType.POSTGRE);
            while (resultSet.next()) {
                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.setColumnName(resultSet.getString("column_name"));
                columnInfo.setColumnType(resultSet.getString("column_type"));
                columnInfo.setColumnDataType(resultSet.getString("data_type"));
                columnInfo.setColumnComment(resultSet.getString("column_comment"));
                columnInfo.setColumnNullable(BooleanUtils.parse(resultSet.getString("is_nullable")));
                //columnInfo.setColumnDefault(resultSet.getString("column_default"));
                //columnInfo.setColumnKey(resultSet.getString("column_key"));

                columnInfo.setJavaFieldName(StringUtil.textToCamelCase(columnInfo.getColumnName(),false));
                String temp = mapping.get(columnInfo.getColumnDataType());
                columnInfo.setJavaFieldType(StringUtils.isNotBlank(temp)? temp : "Object");

                rs.add(columnInfo);
            }
            return rs;
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }


    public static void main(String[] args) {
        ConnectionInfo connectionInfo = new ConnectionInfo();
        connectionInfo.setDbType(DbType.POSTGRE)
                .setName("数据中心")
                .setHost("t203")
                .setPort(5432)
                .setUsername("postgres")
                .setPasswd("admin123ttyj7890uiop")
        ;

        PostgreDatabaseDao dao = new PostgreDatabaseDao(connectionInfo);
        List<DatabaseInfo> databaseInfoList = dao.getDatabaseInfoList();
        System.out.println(databaseInfoList);

        List<TableInfo> tableInfoList = dao.getTableInfoList(databaseInfoList.get(0).databaseName,null);
        System.out.println(tableInfoList);

        List<ColumnInfo> columnInfoList = dao.getColumnInfoList(databaseInfoList.get(0).databaseName, null, tableInfoList.get(0).getTableName());
        System.out.println(columnInfoList);


    }
}
